''' <summary>
''' Contains grid with some data to indicate which Working or Master data table is currently active
''' </summary>
''' <remarks>Also used to display all selected data</remarks>
Public Class frmReport
    Inherits System.Windows.Forms.Form

    Private MyDataReader As DbDataReader

    Private WithEvents bwGetDataSource As System.ComponentModel.BackgroundWorker
    Private _ReportName As String
    Private _ColumnHeadings As String
    Private _ForceLandscape As Boolean
    Private _AllowWordWrap As Boolean
    Private _IsCrossTab As Boolean
    Private MaxRecords As Integer = 1000
    Private IsFillingGrid As Boolean = False

    Private Sub UpdateComboBox()
        Dim NumRecords As Long = Project.DB.GetRecordCount(clsProject.RptData)
        With Me.cboNumRecords
            Dim LastIndex As Integer = .SelectedIndex
            .Items.Clear()
            Dim num As Integer = Min(NumRecords, 1000)
            If num >= 1000 Then
                Do
                    Dim s As String = IIf(num = NumRecords, "all", "first")
                    If num <> 0 Then .Items.Add(String.Format("Show {0} {1} records", s, num))
                    num *= 5
                Loop Until num >= NumRecords
            End If
            If NumRecords = 0 Then
                .SelectedIndex = -1
                .Text = "No records selected"
            Else
                .Items.Add(String.Format("Show all {0} records", NumRecords))
                .SelectedIndex = Min(.Items.Count - 1, Max(0, Min(.Items.Count - 1, LastIndex)))
            End If
        End With
    End Sub

    Friend Sub FillGrid(ByVal FillInBackground As Boolean)

        Control.CheckForIllegalCrossThreadCalls = True
        IsFillingGrid = True

        MainForm.WriteProgress("Loading data...")

        If FillInBackground Then
            bwGetDataSource = New System.ComponentModel.BackgroundWorker
            With bwGetDataSource
                .WorkerSupportsCancellation = True
                dgBrowser.DataSource = Nothing
                .RunWorkerAsync()
            End With
        Else
            GetDataSource()
            FinishFillGrid()
        End If

    End Sub

    Private Sub GetDataSource()
        Try
            ' set up connection string
            With Project
                Dim sql As String
                Select Case .DB.DatabaseType
                    'todo: see about moving these case statements out & making properties
                    Case clsDB.enumDBType.Firebird, clsDB.enumDBType.Firebird_Embedded
                        sql = String.Format("SELECT FIRST {1} * from {0}", clsProject.RptData, MaxRecords)
                    Case clsDB.enumDBType.SQLite, clsDB.enumDBType.MySQL
                        sql = String.Format("SELECT * FROM {0} LIMIT {1}", clsProject.RptData, MaxRecords)
                    Case clsDB.enumDBType.Oracle
                        sql = String.Format("SELECT * FROM {0} WHERE CAST(ROWNUM AS INTEGER)<={1}", clsProject.RptData, MaxRecords)
                    Case Else
                        sql = String.Format("SELECT TOP {1} * from {0}", clsProject.RptData, MaxRecords)
                End Select

                ' retrieve data into DataSet
                If .DataSet Is Nothing Then .DataSet = New DataSet
                If .DataSet.Tables.Contains(clsProject.RptData) Then
                    .DataSet.Tables(clsProject.RptData).Dispose()
                    .DataSet.Tables.Remove(clsProject.RptData)
                End If
                'Control.CheckForIllegalCrossThreadCalls = False
                .DB.AddTable(.DataSet, clsProject.RptData, sql)
                'todo: test this to be sure don't need to set headers
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub FinishFillGrid()
        Try
            MainForm.WriteProgress("Filling grid with data...")

            With dgBrowser
                .SuspendLayout()
                .DataSource = Nothing
                .Font = New Font("Tahoma", 8, FontStyle.Regular, GraphicsUnit.Point)
                .ReadOnly = True
                .AllowUserToOrderColumns = False
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToAddRows = False
                .RowHeadersVisible = False
                .ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
                .SelectionMode = DataGridViewSelectionMode.FullRowSelect
                .StandardTab = False
                .ColumnHeadersDefaultCellStyle.WrapMode = IIf(_AllowWordWrap, DataGridViewTriState.True, DataGridViewTriState.False)
                .DefaultCellStyle.WrapMode = IIf(_AllowWordWrap, DataGridViewTriState.True, DataGridViewTriState.False)
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None
                .AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCells)

                Dim dt As DataTable = Project.DataSet.Tables(clsProject.RptData)
                If dt Is Nothing Then Exit Sub

                'replace \n characters with vbcrlf in data
                For Each r As DataRow In dt.Rows
                    For c As Integer = 0 To dt.Columns.Count - 1
                        If dt.Columns(c).DataType Is GetType(String) Then
                            r.Item(c) = r.Item(c).ToString.Replace("\n", vbCrLf)
                        End If
                    Next
                Next

                .DataSource = dt

                .AutoResizeColumnHeadersHeight()

                Dim DateTimeColumnFound As Boolean = False

                For c As Int16 = 0 To .ColumnCount - 1
                    With .Columns(c)
                        If .Name.ToUpper = "RECID" Then .Visible = False 'cross-tab reports have primary key called RecID (reqd for Paradox)
                        .DefaultCellStyle.WrapMode = IIf(_AllowWordWrap, DataGridViewTriState.True, DataGridViewTriState.False)
                        If _ColumnHeadings <> "" Then
                            .HeaderText = _ColumnHeadings.Split(",")(c).Replace("\n", vbCrLf)
                        Else
                            .HeaderText = dt.Columns(c).Caption.Replace("\n", vbCrLf)
                        End If
                        With .HeaderCell.Style
                            .BackColor = Drawing.SystemColors.ControlDarkDark
                            .ForeColor = Color.White
                            .Font = New Font(dgBrowser.DefaultCellStyle.Font, FontStyle.Bold)
                        End With

                        .SortMode = DataGridViewColumnSortMode.NotSortable
                        If dt.Columns(c).DataType Is GetType(String) Then
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft
                            'for multi-row reports, numeric values are converted to string and vbcr(\n) inserted--still want these aligned right (will check for special contents of Summarize by Parameter Stats)
                            If dt.Rows.Count > 0 AndAlso CStr(dt.Rows(0).Item(c)).Contains(vbCr) Then
                                Dim IsNum As Boolean = True
                                For Each s As String In CStr(dt.Rows(0).Item(c)).Split(vbCrLf)
                                    If s.Trim <> "" And s <> "N/A" And Not s.EndsWith("%)") And Not IsNumeric(s) Then IsNum = False : Exit For
                                Next
                                If IsNum Then .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight
                            End If
                            'always want results column right aligned even if string (RCode prepended)
                            If dt.Columns(c).Caption = "RESULT" Or dt.Columns(c).Caption = "VALUE" Then .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight
                            'for crosstab report columns after date-time, align right
                            If _IsCrossTab And DateTimeColumnFound Then .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight
                        ElseIf dt.Columns(c).DataType Is GetType(Boolean) Then
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter
                        ElseIf dt.Columns(c).DataType Is GetType(Date) Then
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter
                            .DefaultCellStyle.Format = MyDateTimeFormat()
                            DateTimeColumnFound = True
                        ElseIf dt.Columns(c).DataType Is GetType(Double) Or dt.Columns(c).DataType Is GetType(Single) Or dt.Columns(c).DataType Is GetType(Decimal) Then
                            'todo: consider if format can depend on format code in pcodes table
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                            .DefaultCellStyle.Format = "N3"
                        Else 'integer
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                        End If
                    End With
                Next

                If .Columns.Contains("Sig_Figs") Then .Columns("Sig_Figs").Visible = False

                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                .AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCells)

                'look for special text in second column (first is RecID) that indicates is summary row and reset the style
                For Each row As DataGridViewRow In .Rows
                    If row.Cells(1).Value.ToString.StartsWith("Summary", StringComparison.OrdinalIgnoreCase) Then row.DefaultCellStyle.BackColor = Color.LightGray
                Next

            End With

        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            dgBrowser.ResumeLayout(True)
            MainForm.ProgressComplete()
            UpdateComboBox()
            MainForm.StatusBarPanelText.Text = String.Format("{0} records are displayed", MyNumRecords)
            IsFillingGrid = False
        End Try
    End Sub

    Private Sub dgBrowser_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgBrowser.CellFormatting
        'If Project.DB.DatabaseType = clsDB.enumDBType.SQL_Server Then 'for some reason, does not obey datagridview nullvalue setting
        '    If e.ColumnIndex >= 0 AndAlso dgBrowser.Item(e.ColumnIndex, e.RowIndex).Value.isnull Then e.Value = "" : e.FormattingApplied = True
        'End If
        With dgBrowser
            If .Columns(e.ColumnIndex).Name.Contains("Result") AndAlso .Columns.Contains("Sig_Figs") Then
                Dim SigFigs As Object = .Item(.Columns("Sig_Figs").Index, e.RowIndex).Value
                If IsNumeric(SigFigs) Then e.CellStyle.Format = "N" & CInt(SigFigs)
            End If
        End With
    End Sub

    Friend ReadOnly Property MyNumRecords() As Integer
        Get
            Return Me.dgBrowser.RowCount
        End Get
    End Property

    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub frmBrowser_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Activated
        'If Me.MdiParent Is Nothing Then Me.ShowInTaskbar = True
    End Sub

    Private Sub bwGetDataSource_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwGetDataSource.DoWork
        GetDataSource()
    End Sub

    Private Sub bwGetDataSource_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwGetDataSource.RunWorkerCompleted
        FinishFillGrid()
        Me.bwGetDataSource.Dispose()
        Me.bwGetDataSource = Nothing
    End Sub

    Private Sub frmBrowser_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        If Me.bwGetDataSource IsNot Nothing Then
            Me.bwGetDataSource.CancelAsync()
            Me.bwGetDataSource.Dispose()
            Me.bwGetDataSource = Nothing
            If Project.DataSet.Tables.Contains(clsProject.RptData) Then
                Project.DataSet.Tables(clsProject.RptData).Dispose()
                Project.DataSet.Tables.Remove(clsProject.RptData)
            End If
            Project.DB.Connection.Close()
            MainForm.ProgressComplete()
        End If
        SaveWindowPos(REGAPP, Me)
    End Sub

    Private Sub frmBrowser_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Me.KeyPress
        If Me.bwGetDataSource IsNot Nothing Then
            If e.KeyChar = Chr(27) Then AskCancel()
            If ProgressCancel Then
                Me.bwGetDataSource.CancelAsync()
                Me.bwGetDataSource.Dispose()
                Me.bwGetDataSource = Nothing
                'Project.DataSet.Tables.Remove(TableName)
                If Project.DB.Connection IsNot Nothing AndAlso Project.DB.Connection.State = ConnectionState.Open Then Project.DB.Connection.Close()
                MainForm.ProgressComplete()
                Me.Close()
            End If
        End If
    End Sub

    Private Sub cboNumRecords_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cboNumRecords.KeyDown
        If e.KeyCode = Keys.Enter Then UpdateNumRecords()
    End Sub

    Private Sub cboNumRecords_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboNumRecords.SelectionChangeCommitted
        'text doesn't get changed immediately--force it
        With cboNumRecords
            .Text = .Items(.SelectedIndex)
        End With
        UpdateNumRecords()
    End Sub

    Private Sub UpdateNumRecords()
        Dim txt As String = Me.cboNumRecords.Text
        If Val(txt) <> 0 Then 'a number was typed in
            Me.cboNumRecords.Text = String.Format("Show first {0} records", Val(txt))
        Else
            Dim ar() As String = txt.Split(" ")
            If ar.Length >= 3 AndAlso Val(ar(2)) <> 0 Then
                txt = ar(2)
            Else
                Me.cboNumRecords.SelectedIndex = 0
                txt = "1000"
            End If
        End If
        MaxRecords = Val(txt)
        If clsProject.RptData IsNot Nothing Then FillGrid(True)
        Me.dgBrowser.Focus()
    End Sub

    ''' <summary>
    ''' Instantiate form
    ''' </summary>
    ''' <param name="ReportName">Title to be displayed on form and page heading if printed</param>
    ''' <param name="ColumnHeadings">Comma-separted list of friendly column headings for grid</param>
    ''' <param name="ForceLandscape">Force printed report to be landscape</param>
    ''' <param name="AllowWordWrap">If true, cells can have embedded returns and rows will get taller</param>
    ''' <param name="IsCrossTab">If true, will allow font size to reduce if many columns, and combined RCode/Result columns will be right-aligned</param>
    Public Sub New(ByVal ReportName As String, ByVal ColumnHeadings As String, ByVal ForceLandscape As Boolean, ByVal AllowWordWrap As Boolean, ByVal IsCrossTab As Boolean)
        Me.New()
        _ReportName = ReportName
        _ColumnHeadings = ColumnHeadings
        _ForceLandscape = ForceLandscape
        _AllowWordWrap = AllowWordWrap
        _IsCrossTab = IsCrossTab
    End Sub

    Private Sub frmBrowser_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'dgvEditor = New WRDB.Controls.DGVEditor(dgBrowser)
        GetWindowPos(REGAPP, Me)
        FillGrid(True)
        Text = _ReportName
    End Sub

    'Private Sub dgvEditor_ProgressChanged(ByVal sender As Object, ByVal Percent As Integer) Handles dgvEditor.ProgressChanged
    '    If Not WriteProgress(Percent, 100, "Sending to Excel...", ProgressBarStyle.Blocks) Then dgvEditor.CancelOperation()
    'End Sub

    'Private Sub dgvEditor_ProgressComplete(ByVal sender As Object) Handles dgvEditor.ProgressComplete
    '    WriteProgress(True)
    'End Sub

    Private Sub btnPrint_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrint.Click, btnPreview.Click, btnPDF.Click
        Try
            If MainWRDB.ReportHeading1 = "" And MainWRDB.ReportHeading2 = "" Then WarningMsg("You have not yet entered the default report heading text which indicates your organization name and location. This is done using File | Project Preferences.")
            Dim ReportComments As String = ""
            With New frmReportComments
                If .ShowDialog(Me) = Windows.Forms.DialogResult.Cancel Then Exit Sub
                ReportComments = .txtComments.Text
            End With
            Dim Preview As Boolean = (CType(sender, Control).Name = btnPreview.Name)
            Dim MakePDF As Boolean = (CType(sender, Control).Name = btnPDF.Name)
            Dim dgvPrinter As New WRDB.Controls.DGVPrinter(dgBrowser, PrintDoc)
            With dgvPrinter
                .AllowWordWrap = False
                .AutoReduceFont = _IsCrossTab
                .ProportionalColumns = True
                .Title = Project.Name
                .TitleFont = New Font(.TitleFont, FontStyle.Bold)
                .SubTitle = MainWRDB.ReportHeading1 & vbCrLf & MainWRDB.ReportHeading2
                .SubTitle2 = _ReportName
                .SubTitle2Font = New Font(.SubTitleFont, FontStyle.Underline)
                .PageNumberFont = New Font(.PageNumberFont, FontStyle.Italic)
                .PageNumberAlignment = StringAlignment.Near
                .PageNumberInHeader = False
                .FooterAlignment = StringAlignment.Far
                .FooterFont = New Font(.PageNumberFont, FontStyle.Italic)
                .Footer = Now.ToString("MMMM d, yyyy")
                .EndNoteFont = New Font(.EndNoteFont, FontStyle.Italic)
                .EndNote = ReportComments
                .RepeatTitles = False
                .ForceLandscape = _ForceLandscape
                If MakePDF Then
                    .PrintDataGridViewToPDF(True, Text)
                Else
                    .PrintDataGridView(Preview)
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCopy.Click
        With dgBrowser
            '.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText 'required for clipboard usage
            .ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
            .SelectAll()
            My.Computer.Clipboard.SetDataObject(.GetClipboardContent)
        End With
    End Sub

    Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim db As clsDB = Nothing
        Try
            'MainForm.OutputExport(RptData)
            Dim FileName As String
            With New SaveFileDialog
                .AddExtension = True
                .CheckPathExists = True
                .InitialDirectory = AppDataPath
                .FileName = Text
                .SupportMultiDottedExtensions = False
                '.DefaultExt = ".txt"
                .Filter = "Tab-separated Text File (*.txt)|*.txt|Excel Spreadsheet File (*.xls*)|*.xls*"
                .FilterIndex = GetSetting(REGAPP, Name, "FilterIndex", 1)
                .OverwritePrompt = False
                .ValidateNames = True
                If .ShowDialog(Me) = DialogResult.OK Then
                    FileName = .FileName
                    If IO.Path.GetExtension(FileName) = "" Then
                        FileName &= Choose(.FilterIndex, ".txt", ".xls")
                    End If
                    SaveSetting(REGAPP, Name, "FilterIndex", .FilterIndex)
                Else
                    Exit Sub
                End If
            End With

            Dim TableName As String
            Dim dt As DataTable = Project.DataSet.Tables(clsProject.RptData)

            If FileName.EndsWith(".txt", StringComparison.OrdinalIgnoreCase) Then
                If My.Computer.FileSystem.FileExists(FileName) Then
                    If MessageBox.Show("The selected file already exists; do you want to overwrite it?", "Save Report", MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.Cancel Then Exit Sub
                End If
                MainForm.WriteProgress("Saving report", ProgressBarStyle.Marquee)
                If cboNumRecords.SelectedIndex <> cboNumRecords.Items.Count - 1 Then
                    cboNumRecords.SelectedIndex = cboNumRecords.Items.Count - 1
                    UpdateNumRecords()
                    Do
                        Application.DoEvents()
                    Loop Until Not IsFillingGrid
                    dt = Project.DataSet.Tables(clsProject.RptData)
                End If
                Dim sw As New IO.StreamWriter(FileName, False)
                For c As Integer = 0 To dt.Columns.Count - 1
                    If c > 0 Then sw.Write(vbTab)
                    sw.Write(dt.Columns(c).ColumnName)
                Next
                sw.WriteLine()
                For r As Integer = 0 To dt.Rows.Count - 1
                    For c As Integer = 0 To dt.Columns.Count - 1
                        If c > 0 Then sw.Write(vbTab)
                        sw.Write(TestNull(dt.Rows(r).Item(c), ""))
                    Next
                    sw.WriteLine()
                    Application.DoEvents()
                Next
                sw.Close()
                sw.Dispose()
            Else
                db = New clsDBExcel
                With db
                    .DatabaseType = clsDB.enumDBType.Excel
                    .DataSource = FileName
                    .CommandTimeout = 5
                    .CreateConnection()
                    TableName = "Report"
                    If My.Computer.FileSystem.FileExists(FileName) Then
                        Select Case MessageBox.Show("The selected file already exists; do you want to overwrite it?" & vbCr & vbCr & "(Select 'No' to be prompted for a new worksheet name to be created and added to the workbook)", "Export Table", MessageBoxButtons.YesNoCancel)
                            Case Windows.Forms.DialogResult.Yes
                                My.Computer.FileSystem.DeleteFile(FileName)
                            Case Windows.Forms.DialogResult.No
                                TableName = InputBox(String.Format("This spreadsheet already contains the following worksheet names:\n\n{0}\n\nEnter the desired name of a new worksheet or the worksheet name you want to overwrite:".Replace("\n", vbCrLf), ArrayToCSV(db.GetTableList).Replace(",", ", ")), "Export Table", TableName)
                                If TableName = "" Then Exit Sub
                            Case Windows.Forms.DialogResult.Cancel
                                Exit Sub
                        End Select
                    End If
                    'AddHandler .Progress, AddressOf MainForm.Project_Progress
                    If cboNumRecords.SelectedIndex <> cboNumRecords.Items.Count - 1 Then
                        cboNumRecords.SelectedIndex = cboNumRecords.Items.Count - 1
                        UpdateNumRecords()
                        Do
                            Application.DoEvents()
                        Loop Until Not IsFillingGrid
                        dt = Project.DataSet.Tables(clsProject.RptData)
                    End If
                    .SaveTable(TableName, dt)
                    .Connection.Close()
                End With
            End If
            Process.Start(FileName)
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
            If db IsNot Nothing Then
                With db
                    .Connection.Close()
                    .Dispose()
                    'RemoveHandler .Progress, AddressOf MainForm.Project_Progress
                End With
            End If
        End Try
    End Sub
End Class
